﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLServerHealthCheck.Model;

namespace SQLServerHealthCheck.SQLServer.IssueTypes
{
    public class SecurityCheckUserOrphanedDatabaseUsers : IssueTypeCheckBase
    {
        public SecurityCheckUserOrphanedDatabaseUsers()
        {
            IssueTypeId = 9001;
        }

        public override void Check()
        {
            HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);
            Issue issue;

            /*
             * This rule checks if there exist user accounts with no associated Login
             */
            var qryLeftjoin = from User in dc.DatabaseUsers
                              join login in dc.Logins on
                                new { Sid = User.SID, ServiceId = User.Database.ServiceID }
                                    equals
                                new { Sid = login.SID, ServiceId = login.ServiceId }
                              into JoinedUserLog
                              from loginwithEmpty in JoinedUserLog.DefaultIfEmpty()
                              where loginwithEmpty == null && User.Name != "guest" && User.Name != "INFORMATION_SCHEMA"
                              && User.Name != "sys" && User.Name != "MS_DataCollectorInternalUser"

                              select new
                              {
                                  User.Database.DatabaseName,

                                  User.Name,
                                  User.SID,
                                  User.DatabaseUserId,
                                  Log_Name = loginwithEmpty != null ? loginwithEmpty.Name : null,
                                  loginSID = loginwithEmpty != null ? loginwithEmpty.SID : null,
                                  LoginServiceId = loginwithEmpty != null ? loginwithEmpty.ServiceId : 0,

                              };
                   
            foreach (var User in qryLeftjoin)
            {
                IssueType issueType = dc.IssueTypes.FirstOrDefault(i => i.IssueTypeId == IssueTypeId);

                //Create new Issue

                issue = new Issue();
                issue.IssueType = issueType;
                issue.TargetId =  User.DatabaseUserId;
                issue.Description = issueType.Description.Replace("{DatabaseUser}",User.Name).Replace("{DatabaseName}",User.DatabaseName);

                dc.Issues.InsertOnSubmit(issue);
            }
            dc.SubmitChanges();
        }

    }

}
